package org.compiere.process;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;

import org.compiere.model.MBSCCard;
import org.compiere.model.MPeriod;
import org.compiere.util.DB;
import org.compiere.util.Env;

public class BSCOpenPeriod extends SvrProcess {

	private List<MBSCCard> cards = new ArrayList<MBSCCard>();
	
	private int C_Period_ID = 0;
	private MPeriod period = null;
	private int AD_Client_ID = 0 ; // Env.getAD_Client_ID(getCtx());
	private int AD_Org_ID = 0; // Env.getAD_Org_ID(getCtx());
	private int C_DocType_ID = 0;

	@Override
	protected void prepare() {
		AD_Client_ID = Env.getAD_Client_ID(getCtx());
		AD_Org_ID = Env.getAD_Org_ID(getCtx());
		C_DocType_ID = getC_DocType_ID();
		initParameters();	
	}

	@Override
	protected String doIt() throws Exception {
		BSCOpen();
		return null;
	}

	private void BSCOpen() {
		if (getCards() != null) {
			for(MBSCCard card:getCards()) {
				if(!card.isDSPKCard())
					openCard(card);
			}
		}
		
	}

	private boolean openCard(MBSCCard card) {
		card.setDocStatus(MBSCCard.DOCSTATUS_Ввод);
		card.setDocAction(MBSCCard.DOCACTION_НаПроверку);
		if (!card.isDSPKCard())
			card.sendMailOpenPeriod();
		return card.save();
	}
	
	public int getC_DocType_ID() {
		StringBuffer sql = new StringBuffer();
		String clientCheck = " AND AD_Client_ID=" + getAD_Client_ID();
		sql.setLength(0);
        sql.append("SELECT C_DocType_ID FROM C_DocType"
		+ " WHERE Name='BSC_Card' AND isActive ='Y'"+clientCheck);        
        C_DocType_ID = DB.getSQLValue(get_TrxName(), sql.toString());
		return C_DocType_ID;
	}

	private void initParameters() {
		for(ProcessInfoParameter param: getParameter()) {
			if (param.getParameterName().equals("C_Period_ID")) {
				setC_Period_ID(Integer.parseInt(param.getParameter().toString()));
			} else if (param.getParameterName().equals("DateFrom") && C_Period_ID == 0) {
				Timestamp dateFrom = (Timestamp) param.getParameter(); 
				setC_Period_ID(getPeriodFromDate(dateFrom));
			}
		}
		loadCards();
	}
	
	private int getPeriodFromDate(Timestamp dateFrom) {
		int result = 0;
		String sql = "select * from C_Period where C_Year_ID in (select C_Year_ID from C_Year where C_Calendar_ID = 1000002) and Cast(EndDate + interval '1 day' as date) = Cast('"+dateFrom+"' as Date)";
		result = DB.getSQLValue(get_TrxName(), sql);
		return result;
	}

	private void loadCards() {
		cards.clear();
		if (getC_Period_ID() > 0) {
			String sql = " SELECT * " +
					     " FROM BSC_Card " +
					     " WHERE AD_Org_ID = ? " +
					     "   AND AD_Client_ID = ? " +
					     "   AND isActive = 'Y' " +
					     "   AND C_Period_ID = ?" +
					     "   AND DocStatus = 'NA'" +
					     "   AND DocAction = '--' ";
		    PreparedStatement pstmt = null;
			ResultSet rs = null;
			try {
				pstmt = DB.prepareStatement(sql,null);
				pstmt.setInt(1, AD_Client_ID);
				pstmt.setInt(2, AD_Org_ID);
				pstmt.setInt(3, C_Period_ID);
				rs = pstmt.executeQuery();
				while (rs.next()) {
					MBSCCard card = new MBSCCard(getCtx(), rs, get_TrxName());
					cards.add(card);
				}
			} catch (SQLException e) {
				log.log(Level.SEVERE, "BSCOpenPeriod", e);
			} finally {
				DB.close(rs, pstmt);
				rs = null; pstmt = null;
			}	
		}
	}

	public int getC_Period_ID() {
		return C_Period_ID;
	}

	public void setC_Period_ID(int c_Period_ID) {
		C_Period_ID = c_Period_ID;
		MPeriod p = MPeriod.get(getCtx(),C_Period_ID);
		if (p != null) {
			setPeriod(p);
		}
	}

	public MPeriod getPeriod() {
		if (period == null && getC_Period_ID() > 0) {
			period = MPeriod.get(getCtx(),getC_Period_ID());;
		}
		return period;
	}

	public void setPeriod(MPeriod period) {
		this.period = period;
	}

	public List<MBSCCard> getCards() {
		return cards;
	}

	public void setCards(List<MBSCCard> cards) {
		this.cards = cards;
	}
}
